package sy.service.impl;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import sy.dao.RolestorerDaoI;
import sy.dao.StorerDaoI;
import sy.model.Trolestorer;
import sy.model.Tstorer;
import sy.pageModel.*;
import sy.service.StorerServiceI;
import sy.util.excel.ExcelWorkSheet;

@Service
public class StorerServiceImpl implements StorerServiceI {

	@Autowired
	private StorerDaoI storerDao;
	@Autowired
	private RolestorerDaoI rolestorerDao;




	@Override
	public DataGrid dataGrid(Storer storer, PageHelper ph) {
		DataGrid dg = new DataGrid();
		List<Storer> ul = new ArrayList<Storer>();
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = " from Tstorer t ";
		List<Tstorer> l = storerDao.find(hql + whereHql(storer, params) + orderHql(ph), params, ph.getPage(), ph.getRows());
		if (l != null && l.size() > 0) {
			for (Tstorer t : l) {
				Storer u = new Storer();
				BeanUtils.copyProperties(t, u);
				ul.add(u);
			}
		}
		dg.setRows(ul);
		dg.setTotal(storerDao.count("select count(*) " + hql + whereHql(storer, params), params));
		return dg;
	}

	private String whereHql(Storer storer, Map<String, Object> params) {
		String hql = "";
		if (storer != null) {
			hql += " where 1=1 and t.storer !=null ";
			if (storer.getName() != null) {
				hql += " and t.name like :name";
				params.put("name", "%%" + storer.getName() + "%%");
			}
			if (storer.getCode() != null) {
				hql += " and t.code like :code";
				params.put("code", "%%" + storer.getCode() + "%%");
			}

		}
		return hql;
	}

	private String orderHql(PageHelper ph) {
		String orderString = "";
		if (ph.getSort() != null && ph.getOrder() != null) {
			orderString = " order by t." + ph.getSort() + " " + ph.getOrder();
		}
		return orderString;
	}

	@Override
	synchronized public void add(Storer storer) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("code", storer.getCode());
		if (storerDao.count("select count(*) from Tstorer t where t.code = :code", params) > 0) {
			throw new Exception("合作伙伴编码已存在！");
		} else {
			Tstorer tstorer = new Tstorer();
			BeanUtils.copyProperties(storer, tstorer);
			tstorer.setStorer(storerDao.get(Tstorer.class,storer.getType()));
			tstorer.setCreateTime(new Date());
			storerDao.save(tstorer);
		}
	}

	@Override
	public Storer get(String id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		Tstorer t = storerDao.get("select distinct t from Tstorer t where t.id = :id", params);
		Storer storer = new Storer();
		BeanUtils.copyProperties(t,storer);
		return storer;
	}

	@Override
	synchronized public void edit(Storer storer) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", storer.getId());
		params.put("code", storer.getCode());
		if (storerDao.count("select count(*) from Tstorer t where t.code = :code and t.id != :id", params) > 0) {
			throw new Exception("编码已存在！");
		} else {
			Tstorer tstorer = storerDao.get(Tstorer.class, storer.getId());
			tstorer.setStorer(storerDao.get(Tstorer.class,storer.getType()));
			BeanUtils.copyProperties(storer, tstorer);
		}
	}
	@Override
	public void delete(String id) {
		//首先删除已经做了数据权限的rolestorer
		List<Trolestorer> list = rolestorerDao.find("from Trolestorer t where t.tstorer.id = '"+id+"'");
		if(list!=null && list.size()>0){
			for(Trolestorer t : list){
				rolestorerDao.delete(t);
			}
		}
		storerDao.delete(storerDao.get(Tstorer.class, id));
	}

	@Override
	public DataGrid carrierCombogrid(String q, PageHelper ph) {
		if (q == null) {
			q = "";
		}
		DataGrid dg = new DataGrid();
		List<Storer> storers = new ArrayList<Storer>();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("name", "%%" + q.trim() + "%%");
		List<Tstorer> tl = storerDao.find("from Tstorer t where t.type='CARRIER' and t.name like :name order by " + ph.getSort() + " " + ph.getOrder(), params, ph.getPage(), ph.getRows());
		if (tl != null && tl.size() > 0) {
			for (Tstorer t : tl) {
				Storer u = new Storer();
				u.setName(t.getName());
				u.setCode(t.getCode());
				u.setId(t.getId());
				storers.add(u);
			}
		}
		dg.setRows(storers);
		dg.setTotal(storerDao.count("select count(*) from Tstorer t where t.type='CARRIER' and t.name like :name", params));
		return dg;
	
	}

	@Override
	public List<Storer> storercombobox() {

		List<Storer> s = new ArrayList<Storer>();
		List<Tstorer> l =storerDao.find("from Tstorer t where t.type = 'OWNER' order by t.code");
		if (l != null && l.size() > 0) {
			for (Tstorer t : l) {
				Storer st = new Storer();
				st.setId(t.getId());
				st.setCode(t.getCode());
				st.setName(t.getName());
				s.add(st);
			}
		}
		return s;
			
	}
	@Override
	public List<Tree> tree(SessionInfo sessionInfo) {

		List<Tstorer> l = null;
		List<Tree> lt = new ArrayList<Tree>();


		l = storerDao.find("from Tstorer t order by t.storer.id,t.code");

		if (l != null && l.size() > 0) {
			for (Tstorer t : l) {
				Tree tree = new Tree();
				BeanUtils.copyProperties(t, tree);
				tree.setText(t.getName());
				tree.setIconCls("status_online");
				if (t.getStorer() != null) {
					tree.setPid(t.getStorer().getId());
				}
				lt.add(tree);
			}
		}
		return lt;

	}

	@Override
	public String savestorerwithExcel(MultipartFile file) {
		String message = "导入失败";
		Workbook book = null;
		String filetype=file.getContentType();
		//System.out.println(filetype);
		// 判断文件类型
		if (filetype.equals("application/vnd.ms-excel") || filetype.equals("text/plain")|| filetype.equals("application/ms-excel")) {
			try {
				book = new HSSFWorkbook(file.getInputStream());
			} catch (FileNotFoundException e) {
				message = "导入失败未检索到模版数据";
				e.printStackTrace();
			} catch (IOException e) {
				message = "导入失败";
				e.printStackTrace();
			}
		}else{
			return "文件类型错误！请选择excel导入！";
		}
		// book.getNumberOfSheets(); 判断Excel文件有多少个sheet
		Sheet sheet = book.getSheetAt(0);
		ExcelWorkSheet<Tstorer> excelWorkSheet = new ExcelWorkSheet<Tstorer>();
		// 保存工作单名称
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.iterator();
		// 保存列名
		List<String> cellNames = new ArrayList<String>();
		while (iterator.hasNext()) {
			cellNames.add(iterator.next().getStringCellValue());
		}
		excelWorkSheet.setColumns(cellNames);

		int m=0;    //记录导入信息与以存在信息的重复数量，即更新数。

		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			Row ros = sheet.getRow(i);
			Tstorer storer = null;
			String storerCode = "";
			String storerType = "";
			//设置编码
			if (ros.getCell(0) != null && !ros.getCell(0).toString().trim().equals("")) {
				ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				storerCode = ros.getCell(0).getStringCellValue();

				String hql = " from Tstorer t where t.code = '" + storerCode + "'";
				storer = storerDao.get(hql);
				if (storer != null) {
					m++;
				} else {
					storer = new Tstorer();
					storer.setId(UUID.randomUUID().toString());
					storer.setCode(storerCode);
					storer.setType(storerType);
					//设置对应的tstorer
					storer.setStorer(storerDao.get("from Tstorer t where t.id = '" + storerType + "'"));
				}
				//设置名称
				if (ros.getCell(1) != null) {
					ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
					storer.setName(ros.getCell(1).getStringCellValue());
				} else {
					return "第" + (i + 1) + "行名称不能为空！";
				}
				//设置联系电话
				if (ros.getCell(2) != null) {
					ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
					storer.setTel(ros.getCell(2).getStringCellValue());
				}else{
					return "第"+(i+1)+"行联系电话不能为空！";
				}
				//设置联系地址
				if(ros.getCell(3) != null){
					ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
					storer.setAddress(ros.getCell(3).getStringCellValue());
				}else{
					return "第" + (i + 1) + "行联系地址不能为空！";
				}
				//设置类型
				if(ros.getCell(4) != null && !ros.getCell(4).toString().trim().equals("")){
					ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
					storerType = ros.getCell(4).getStringCellValue();
					storer.setStorer(storerDao.get(Tstorer.class,storerType));
					storer.setType(storerType);
				}else{
					return "第"+(i+1)+"行类型不能为空！";
				}

				//设置邮箱
				if(ros.getCell(5) != null){
					ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
					storer.setEmail(ros.getCell(5).getStringCellValue());
				}else{
					return "第"+(i+1)+"行邮箱不能为空！";
				}
				//设置备注
				if(ros.getCell(6) != null){
					ros.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
					storer.setRemark(ros.getCell(6).getStringCellValue());
				}
				//创建时间
				storer.setCreateTime(new Date());
				storerDao.saveOrUpdate(storer);
				int n=sheet.getLastRowNum()-m; //记录添加记录的数量。
				message="导入成功！添加了"+n+"条数据，更新了"+m+"条数据";
			}else{
				return "第"+(i+1)+"行编码不能为空！";
			}
		}
			return message;
		}
}
